package dao.impl;

import dao.EmpDao;
import pojo.Emp;
import util.BaseDao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

/**
 * 员工信息表的接口的实现
 */
public class EmpDaoImpl implements EmpDao {

    /**
     * 查询员工信息
     * @return list结合
     */
    @Override
    public List<Emp> list() {
        BaseDao bd=new BaseDao();
        List<Emp> list=new ArrayList<Emp>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;

        try {
            conn=bd.getConnection();
            String sql="select * from emps";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                Emp ep=new Emp();
                ep.setEname(rs.getString("ename"));
                ep.setEmpno(rs.getInt("empno"));
                ep.setDeptno(rs.getInt("deptno"));
                ep.setJob(rs.getString("job"));
                ep.setHireDate(rs.getTimestamp("hiredate"));
                ep.setComm(rs.getInt("comm"));
                ep.setSalary(rs.getInt("sal"));
                list.add(ep);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            bd.ConnectionClose(conn,ps,rs);
        }
        return list;
    }

    /**
     * 分页查询员工信息
     * @param pageIndex   页码
     * @param pageNumber  当前页码显示的条数
     * @return list集合
     */
    @Override
    public List<Emp> pageList(int pageIndex, int pageNumber) {
        BaseDao bd=new BaseDao();
        List<Emp> list=new ArrayList<Emp>();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=bd.getConnection();
            String sql="select * from("+
                        "select rownum r,e.* from emps e "+
                        "where rownum<=?"+
                        ")where r>?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,pageIndex*pageNumber);
            ps.setInt(2,(pageIndex-1)*pageNumber);
            rs=ps.executeQuery();
            while (rs.next()){
                Emp ep=new Emp();
                ep.setEname(rs.getString("ename"));
                ep.setEmpno(rs.getInt("empno"));
                ep.setDeptno(rs.getInt("deptno"));
                ep.setJob(rs.getString("job"));
                ep.setHireDate(rs.getTimestamp("hiredate"));
                ep.setComm(rs.getInt("comm"));
                ep.setSalary(rs.getInt("sal"));
                list.add(ep);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            bd.ConnectionClose(conn,ps,rs);
        }
        return list;
    }

    /**
     * 统计数据总条数
     * @return 返回数据总条数
     */
    @Override
    public int sumPage() {
        int count=0;
        BaseDao bd=new BaseDao();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn=bd.getConnection();
            String sql="select count(*) from emps";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while (rs.next()){
                count=rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            bd.ConnectionClose(conn,ps,rs);
        }
        return count;
    }

    /**
     *添加员工信息
     */
    @Override
    public int addEmp(Emp emp) {
        int bo=0;
        BaseDao bd=new BaseDao();
        String sql="insert into emps values(?,?,?,?,?,?,?)";
        bo=bd.crud(sql,emp.getEname(),emp.getEmpno(),emp.getDeptno(),emp.getJob(),emp.getHireDate(),emp.getComm(),emp.getSalary());
        return bo;
    }

    /**
     * 修改数据表中所有名叫张三的用户的奖金和佣金
     */
    @Override
    public int modifyEmp(Emp emp) {
        int bo=0;
        BaseDao bd=new BaseDao();
        String sql="update emps set ename=?,deptno=?,job=?,hiredate=?,comm=?,sal=? "+
                        "where empno=?";
        bo=bd.crud(sql,emp.getEname(),emp.getDeptno(),emp.getJob(),emp.getHireDate(),emp.getComm(),emp.getSalary(),emp.getEmpno());
        return bo;
    }

    /**
     * 删除员工
     */
    @Override
    public int deleteEmp(Emp emp) {
        int bo=0;
        BaseDao bd=new BaseDao();
        String sql="delete from emps where empno=?";
        bo=bd.crud(sql,emp.getEmpno());
        return bo;
    }

    /**
     * 查询员工是否存在
     */
    @Override
    public boolean queryIsExit(Emp emp) {
        boolean bo=false;
        BaseDao bd=new BaseDao();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            conn= BaseDao.ds.getConnection();
            String sql="select * from emps where ename=? and empno=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,emp.getEname());
            ps.setInt(2,emp.getEmpno());
            rs=ps.executeQuery();
            bo=rs.next();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            bd.ConnectionClose(conn,ps,rs);
        }
        return bo;
    }

    @Override
    public Emp findEmp(int empno) {
        BaseDao bd=new BaseDao();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Emp ep=new Emp();
        try {
            conn=bd.getConnection();
            String sql="select * from emps WHERE EMPNO=?";
            ps=conn.prepareStatement(sql);
            ps.setInt(1,empno);
            rs=ps.executeQuery();
            while(rs.next()){
                ep.setEname(rs.getString("ename"));
                ep.setEmpno(rs.getInt("empno"));
                ep.setDeptno(rs.getInt("deptno"));
                ep.setJob(rs.getString("job"));
                ep.setHireDate(rs.getTimestamp("hiredate"));
                ep.setComm(rs.getInt("comm"));
                ep.setSalary(rs.getInt("sal"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            bd.ConnectionClose(conn,ps,rs);
        }
        return ep;
    }

    @Override
    public Emp login(String ename, int empno) {
        BaseDao bd=new BaseDao();
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        Emp ep=new Emp();
        try {
            conn=bd.getConnection();
            String sql="select * from emps where ename=? and empno=?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,ename);
            ps.setInt(2,empno);
            rs=ps.executeQuery();
            while(rs.next()){
                ep.setEname(rs.getString("ename"));
                ep.setEmpno(rs.getInt("empno"));
                ep.setDeptno(rs.getInt("deptno"));
                ep.setJob(rs.getString("job"));
                ep.setHireDate(rs.getTimestamp("hiredate"));
                ep.setComm(rs.getInt("comm"));
                ep.setSalary(rs.getInt("sal"));

            }
        } catch (Exception e) {
            e.printStackTrace();
        }


        return ep;
    }


}
